package dvsdk.db;

import dvsdk.form.Util;
import java.sql.*;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

public class Base {
    private Connection connection;
    private Statement statement;
    //private ResultSet resultSet;

    public Base(String db_patch) {
        try {
            Class.forName("org.sqlite.JDBC");
            connection = DriverManager.getConnection("jdbc:sqlite:"+db_patch);
            statement = connection.createStatement();        
        } catch (Exception ex) {
            //System.err.println(ex.getLocalizedMessage());
            if (GlobalConfig.DEBUG) ex.printStackTrace();
            //Logger.getLogger(Base.class.getName()).log(Level.SEVERE, ex.getLocalizedMessage(), ex); 
        }
    }

    public Base() {
        this(GlobalConfig.DB_DIR+GlobalConfig.DB_NAME);
    }
    
    @Deprecated
    public synchronized void init(String base) {
        init(base, "");
    }

    public synchronized void init(String base, String prop) {
        try {
            //Logger.getLogger(Base.class.getName()).log(Level.FINE, "DropDB: {0}", base);
            statement.execute("CREATE TABLE IF NOT EXISTS "+base+" ("+GlobalConfig.STD_FILEDS_CREATE+","+prop+");");
            //Logger.getLogger(Base.class.getName()).log(Level.FINE, null, "CreateDB: "+base+"/"+prop);
        } catch (Exception ex) {
            //System.err.println(ex.getLocalizedMessage());
            if (GlobalConfig.DEBUG) ex.printStackTrace();
            //Logger.getLogger(Base.class.getName()).log(Level.SEVERE, ex.getLocalizedMessage(), ex);
        }
    } 
   
    public synchronized void reinit(String base, String prop) {
        try {
            statement.execute("DROP TABLE IF EXISTS "+base+";");
            //Logger.getLogger(Base.class.getName()).log(Level.FINE, "DropDB: {0}", base);
            statement.execute("CREATE TABLE "+base+" ("+GlobalConfig.STD_FILEDS_CREATE+","+prop+");");
            //Logger.getLogger(Base.class.getName()).log(Level.FINE, null, "CreateDB: "+base+"/"+prop);
        } catch (Exception ex) {
            //System.err.println(ex.getLocalizedMessage());
            if (GlobalConfig.DEBUG) ex.printStackTrace();
            //Logger.getLogger(Base.class.getName()).log(Level.SEVERE, ex.getLocalizedMessage(), ex);
        }
    }
    
    public synchronized ResultSet getAllElementsSync(String baseName){
        try {
            //ResultSet returnSet;
                ResultSet rs = statement.executeQuery("SELECT * FROM "+baseName+";");
                //ResultSetMetaData rsmd = rs.getMetaData();
                //int cols = rsmd.getColumnCount();
                //returnSet = rs;
                //rs.close();
               //ResultSetMetaData rsmd = rs.getMetaData();
                //int cols = rsmd.getColumnCount();
                return rs;
            //return returnSet;
        } catch (Exception ex) {
            //System.err.println(ex.getLocalizedMessage());
            if (GlobalConfig.DEBUG) ex.printStackTrace();
            //Logger.getLogger(Base.class.getName()).log(Level.SEVERE, ex.getLocalizedMessage(), ex);
            return null;
        }
    }
    public synchronized List<BinBase> getAllElementsSync(String base_name, String fields) {
        return getAllElementsSync(base_name, fields, "");
    }
    
    public synchronized List<BinBase> getAllElementsSync(String base_name, String fields, String where) {
        try {
            String sql="SELECT * FROM "+base_name+" "+((where.equals(""))?"":"WHERE "+where)+";";
            if (GlobalConfig.DEBUG) System.out.print(sql);
            ResultSet rs = statement.executeQuery(sql);
            if (GlobalConfig.DEBUG) System.out.println(" [OK]");
            //ResultSet rs = this.getAllElementsSync(base_name);
            //ResultSetMetaData rsmd = rs.getMetaData();
            //int cols = rsmd.getColumnCount();
            //rs.beforeFirst();
            
            List<BinBase> ret = new ArrayList();
            //int cols = rsmd.getColumnCount();
            String[] str_f=GlobalConfig.STR_FIELDS_ARR();
            String[] str_a=fields.split(",");

            //rs.beforeFirst();
            while (rs.next()) {
                BinBase binBase = new BinBase();
                List<BinParam> binParams = new ArrayList();
                //for (int i=0; i<cols;i++) {

                    for (String str : str_f) {
                        binBase.setStdValue(str, rs.getString(str));
                    }

                    for (String str : str_a) {
                        //try {
                        binParams.add(new BinParam(str, rs.getString(str)));
                        //} catch (Exception ex) {
                        //    binParams.add( new BinParam(str, Integer.toString(rs.getInt(str))) );
                        //}
                    }
                    binBase.setParams(binParams);
                    ret.add(binBase);
                //}
            }
            rs.close();
            return ret;
        } catch (Exception ex) {
            ex.printStackTrace();
            return null;
        }
    }
    
    public synchronized ResultSet getNewElementsSync(String baseName, String id, String ida, String idr){
        try {
            //ResultSet returnSet;
            ResultSet rs = statement.executeQuery("SELECT * FROM "+baseName+" WHERE ida LIKE '"+ida+"' AND idr LIKE '"+idr+"' AND id NOT IN ( "+id+" )");
            //returnSet = rs;
            //rs.close();
            
            return rs;
        } catch (Exception ex) {
            //System.err.println(ex.getLocalizedMessage());
            if (GlobalConfig.DEBUG) ex.printStackTrace();
            //Logger.getLogger(Base.class.getName()).log(Level.SEVERE, ex.getLocalizedMessage(), ex);
            return null;
        }
    }
    
    public synchronized ResultSet getExistsElementsSync(String baseName, String id, String ida, String idr){
        try {
            //ResultSet returnSet;
            ResultSet rs = statement.executeQuery("SELECT * FROM "+baseName+" WHERE ida LIKE '"+ida+"' AND idr LIKE '"+idr+"' AND id IN ( "+id+" )");
            //returnSet = rs;
            //rs.close();
            
            return rs;
        } catch (Exception ex) {
            //System.err.println(ex.getLocalizedMessage());
            if (GlobalConfig.DEBUG) ex.printStackTrace();
            //Logger.getLogger(Base.class.getName()).log(Level.SEVERE, ex.getLocalizedMessage(), ex);
            return null;
        }
    }
    
    public synchronized ResultSet getModElementsSync(ResultSet irs, List<BinBase> bases) {
        try {
            //List<BinBase> list = new ArrayList();
            //Date dt;
            for (Iterator<BinBase> it = bases.iterator(); it.hasNext();) {
                BinBase binBase = it.next();
                Integer fid = Util.findElementRS(irs, "id", binBase.getId());
                if (fid>-1) {
                    irs.relative(fid);
                    if (irs.getString("date_mod").equals(binBase.getDate_mod())) {
                        //list.add(binBase);
                        irs.deleteRow();
                    }
                }
            }
            return irs;
        } catch (Exception ex) {
            //System.err.println(ex.getLocalizedMessage());
            if (GlobalConfig.DEBUG) ex.printStackTrace();
            //Logger.getLogger(Base.class.getName()).log(Level.SEVERE, ex.getLocalizedMessage(), ex);
            return null;
        }
    }
    
    public synchronized void SyncAddElements(String baseName, List<BinBase> bases) {
        try {
            String sql="INSERT INTO "+baseName+" ( "+GlobalConfig.STD_FILEDS+","+bases.get(0).getParamsSQL()+" ) VALUES ( "+GlobalConfig.STD_FILEDS_IN+","+bases.get(0).getParamsInsertSQL()+" );";
            if (GlobalConfig.DEBUG) System.out.println(sql);
            PreparedStatement pStatement = connection.prepareStatement(sql);
            for (Iterator<BinBase> it = bases.iterator(); it.hasNext();) {
                BinBase binBase = it.next();
                int pc=1;
                pStatement.setString(pc++, binBase.getId());
                pStatement.setString(pc++, binBase.getIda());
                pStatement.setString(pc++, binBase.getIdr());
                pStatement.setString(pc++, binBase.getState());
                pStatement.setString(pc++, Util.getCurDate());
                pStatement.setString(pc++, Util.getCurDate());
                if (GlobalConfig.DEBUG) System.out.print(binBase.getId()+","+binBase.getIda()+","+binBase.getIdr()+","+binBase.getState()+","+binBase.getDate_add()+","+binBase.getDate_mod()+",  "+binBase.getSQLInsertVal());
                List<BinParam> tParams=binBase.getParams();
                for (Iterator<BinParam> itx = tParams.iterator(); itx.hasNext();) {
                    BinParam binParam = itx.next();
                    pStatement.setString(pc++, binParam.getValue());
                }
                pStatement.addBatch();
            }
            connection.setAutoCommit(false);
            pStatement.executeBatch();
            connection.setAutoCommit(true);
            if (GlobalConfig.DEBUG) System.out.println(" [OK]");
        } catch (Exception ex) {
            //System.err.println(ex.getLocalizedMessage());
            if (GlobalConfig.DEBUG) ex.printStackTrace();
            //Logger.getLogger(Base.class.getName()).log(Level.SEVERE, ex.getLocalizedMessage(), ex);
        }
    }
    
    public synchronized void SyncUpdElements(String baseName, List<BinBase> bases) {
        try {
            String sql="UPDATE "+baseName+" SET "+bases.get(0).getSQLUpdate()+", date_mod=\""+Util.getCurDate()+"\", state=? WHERE id=? AND ida=? AND idr=?";
            if (GlobalConfig.DEBUG) System.out.print(sql);
            PreparedStatement pStatement = connection.prepareStatement(sql);
            for (Iterator<BinBase> it = bases.iterator(); it.hasNext();) {
                BinBase binBase = it.next();
                int pc=1;
                List<BinParam> tParams=binBase.getParams();
                for (Iterator<BinParam> itx = tParams.iterator(); itx.hasNext();) {
                    BinParam binParam = itx.next();
                    pStatement.setString(pc++, binParam.getValue());
                }
                pStatement.setString(pc++, binBase.getState());
                pStatement.setString(pc++, binBase.getId());
                pStatement.setString(pc++, binBase.getIda());
                pStatement.setString(pc++, binBase.getIdr());
                pStatement.addBatch();
            }
            connection.setAutoCommit(false);
            pStatement.executeBatch();
            connection.setAutoCommit(true);
            if (GlobalConfig.DEBUG) System.out.println(" [OK]");
        } catch (Exception ex) {
            //System.err.println(ex.getLocalizedMessage());
            if (GlobalConfig.DEBUG) ex.printStackTrace();
            //Logger.getLogger(Base.class.getName()).log(Level.SEVERE, ex.getLocalizedMessage(), ex);
        }
    }
    
    public synchronized void SyncDelElements(String baseName, List<BinBase> bases) {
        try {
            String sql="DELETE FROM "+baseName+" WHERE id = ?";
            if (GlobalConfig.DEBUG) System.out.print(sql);
            PreparedStatement pStatement = connection.prepareStatement(sql);
            for (Iterator<BinBase> it = bases.iterator(); it.hasNext();) {
                BinBase binBase = it.next();
                pStatement.setString(1, binBase.getId());
                pStatement.addBatch();
            }
            connection.setAutoCommit(false);
            pStatement.executeBatch();
            connection.setAutoCommit(true);
            if (GlobalConfig.DEBUG) System.out.println(" [OK]");
        } catch (Exception ex) {
            //System.err.println(Base.class.getName()+"->"+ex.getLocalizedMessage());
            if (GlobalConfig.DEBUG) ex.printStackTrace();
            //Logger.getLogger(Base.class.getName()).log(Level.SEVERE, ex.getLocalizedMessage(), ex);
        }
    }
    
    public synchronized String SyncCountElements(String baseName,String state) {
        try {
            String ret;
            ResultSet rs = statement.executeQuery("SELECT COUNT(1) AS cnt FROM "+baseName+" WHERE state >="+state);
            ret = rs.getString("cnt");
            rs.close();
            return ret;
        } catch (Exception ex) {
            //System.err.println(ex.getLocalizedMessage());
            if (GlobalConfig.DEBUG) ex.printStackTrace();
            //Logger.getLogger(Base.class.getName()).log(Level.SEVERE, ex.getLocalizedMessage(), ex);
            return null;
        }
    }
    
    public void disconnect() {
        try {
            statement.close();
            connection.close();
        } catch (Exception ex) {
            //System.err.println(ex.getLocalizedMessage());
            if (GlobalConfig.DEBUG) ex.printStackTrace();
            //Logger.getLogger(Base.class.getName()).log(Level.SEVERE, ex.getLocalizedMessage(), ex);
        }
    }
}